
create schema tls
go

create table tls.IdTable(TableName varchar(100), NextId int)
go

--drop table tls.Users
-- delete tls.Users
create table tls.Users(
    Id int not null,
    UserName varchar(50) not null,
    Password varchar(50) not null,
    UserEmailAddress varchar(50),
    Name nvarchar(100),
    LName nvarchar(100),
    MobileNo varchar(15),
    WorkPhone varchar(15),
    VerifyNumber varchar(10),
    IsActive bit)
go

insert into tls.IdTable(TableName, NextId) values('tls.Users', 1)
go

alter table tls.Users add primary key(Id)
go

create table tls.Roles(
    Id smallint not null,
    RoleName varchar(50) not null,
    RoleDescription varchar(255) not null)

GO

alter table tls.Roles add primary key(Id)
go


/* Roles table entries */
INSERT INTO tls.Roles VALUES(1,'Admin', 'Administrator');
INSERT INTO tls.Roles VALUES(2,'User', 'User');
go

-- delete tls.UserRoles
create table tls.UserRoles(
    Id int NOT NULL,
    UserId int NOT NULL,
    RoleId smallint NOT NULL)
GO
alter table tls.UserRoles add primary key(Id)
go


ALTER TABLE tls.UserRoles  
   WITH CHECK ADD CONSTRAINT FK_UserRoles_Roles FOREIGN KEY(RoleId)
REFERENCES tls.Roles (Id)
GO

ALTER TABLE tls.UserRoles  
     WITH CHECK ADD CONSTRAINT FK_UserRoles_Users FOREIGN KEY(UserId)
REFERENCES tls.Users (Id)
GO